﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_GetHtmlPageByUrlById')
BEGIN
    PRINT 'Dropping Procedure proc_cm_GetHtmlPageByUrlById'
    DROP  Procedure  proc_cm_GetHtmlPageByUrlById
END
GO

PRINT 'Creating Procedure proc_cm_GetHtmlPageByUrlById'
GO

CREATE Procedure dbo.proc_cm_GetHtmlPageByUrlById
	@pHtmlPageId uniqueidentifier
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	select
		hp.[html_page_id]
		,hp.[page_url]
		,hp.[html_title_txt]
		,hp.[meta_keyword_txt]
		,hp.[meta_description_txt]
		,hp.[page_title_txt]
		,hp.[created_utc_dttm]
		,hp.[updated_utc_dttm]
		,hp.[parent_html_page_id]
		,hp.[meta_nofollow_ind]
		,hp.[meta_noarchive_ind]
		,hp.[meta_noindex_ind]
		,hp.[use_navigation_path_ind]
		,hp.[alias_page_url]
		,hp.[page_settings]
		,hp.[include_header_image_ind]
		,hp.[header_image_url]
		,sm.[role] 
	from 
		[dbo].[tbl_v2_HtmlPage] hp (nolock)
		left join tbl_sitemap sm (nolock) 
			on hp.page_url = sm.node_url or hp.page_url like '%' + convert(nvarchar(100), sm.node_id) + '%'
	where
		hp.[html_page_id] = @pHtmlPageId
END
GO

GRANT EXEC ON dbo.proc_cm_GetHtmlPageByUrlById TO PUBLIC
GO

